﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using ZHDJ.Core.Caching;

namespace ZHDJ.Data
{
    /// <summary>
    /// 党组织架构
    /// </summary>
    public class PartyOrg
    {

        /// <summary>
        /// 查询成员组织ID
        /// </summary>
        /// <param name="loginID"></param>
        /// <returns></returns>
        public static string QueryMemberBranchID(string loginID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "LoginID",
                    Value = loginID
                }
            };
            string sql = @"SELECT BranchID FROM tblpartyorgmember WHERE LoginID = ?LoginID";
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }




        /// <summary>
        /// 获取最顶层组织ID
        /// </summary>
        /// <returns></returns>
        public static string QueryTopBranchId()
        {
            MemoryCacheManager Cache = new MemoryCacheManager();
            return Cache.Get(CacheKeys.Cache_TopBranchID, () =>
            {
                string sql = @"SELECT BranchID FROM tblpartyorgBranch WHERE IfNULL(ParentBranchID,'') = '' ";
                var obj = MySqlHelper.ExecuteScalar(sql);
                return obj != null ? obj.ToString() : "";
            });
        }

        /// <summary>
        /// 查询成员组织ID
        /// </summary>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static string QueryBranchName(string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchID",
                    Value = branchID
                }
            };
            string sql = @"SELECT BranchName FROM tblPartyOrgBranch WHERE BranchID =?BranchID";
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }

        /// <summary>
        /// 查询组织BranchMark
        /// </summary>
        /// <returns></returns>
        public static string QueryBranchMarkEx(string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchID",
                    Value = branchID
                }
            };
            string sql = "SELECT BranchMark FROM tblpartyorgbranch WHERE BranchID =?BranchID";
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }

        public static string QueryBranchID(string currentBranchName)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchName",
                    Value = currentBranchName
                }
            };
            string sql = @"SELECT BranchID FROM tblPartyOrgBranch WHERE BranchName =?BranchName";
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }
        /// <summary>
        /// 查询上级组织ID
        /// </summary>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static string QueryParentBranchID(string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchID",
                    Value = branchID
                }
            };
            string sql = @"SELECT ParentBranchID FROM tblPartyOrgBranch WHERE BranchID =?BranchID";
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }

        /// <summary>
        /// 查询上级组织ID
        /// </summary>
        /// <param name="name">当前组织名称</param>
        /// <returns></returns>
        public static string QueryParentBranchIDBybranchname(string currentBranchName)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchName",
                    Value = currentBranchName
                }
            };
            string sql = @"SELECT ParentBranchID FROM tblPartyOrgBranch WHERE BranchName =?BranchName";
            Object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }
        /// <summary>
        /// 获取党工委组织ID
        /// </summary>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static string GetPartyAppointArray(string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchID",
                    Value = branchID
                }
            };
            string sql = string.Format(@" call sp_GetPartyAppointArray(?BranchID); ");
            object obj = MySqlHelper.ExecuteScalar(sql, parameters.ToArray());
            if (obj != null) return obj.ToString();
            else return "";
        }

        /// <summary>
        /// 获取组织管理员
        /// </summary>
        /// <param name="branchID"></param>
        /// <returns></returns>
        public static DataTable GetBranchAdmin(string branchID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "BranchID",
                    Value = branchID
                }
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@" drop table if EXISTS tempTbladmins;
create temporary table  tempTbladmins  
SELECT LoginID  from(
SELECT LoginID FROM tblpartyorgbranchadmin WHERE BranchID = ?BranchID
union 
SELECT LoginID FROM tblPartyOrgMember WHERE BranchID = ?BranchID and IsPartyWorker = 1
       ) branchadmins;                       
select distinct tempTbladmins.LoginID,a.MobileNo,a.UserName,BranchID from tempTbladmins left join tblPartyOrgMember
a on(a.LoginID=tempTbladmins.LoginID)
where IfNULL(a.mobileno,'')<>'' and IfNULL(a.UserName,'')<>'';");
            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 获取人员管理的组织ID
        /// </summary>
        public static DataTable GetBranchManageByLoginId(string loginId, bool isDown = false)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "LoginID",
                    Value = loginId
                }
            };
            StringBuilder sb = new StringBuilder();
            sb.Append(@" drop table if EXISTS tempTblBranchID;
                    create temporary table  tempTblBranchID 
                    select * from (
                     SELECT ob.BranchID, ob.BranchName, BranchMark FROM tblPartyOrgMember m
                    INNER JOIN tblPartyOrgBranch ob ON ob.BranchID = m.BranchID
                    WHERE  m.LoginID =?LoginID and m.ispartyworker=1
                    union 
                    SELECT ob.BranchID, ob.BranchName,BranchMark FROM tblPartyOrgBranchAdmin ba
                    INNER JOIN tblPartyOrgBranch ob ON ob.BranchID = ba.BranchID
                    WHERE ba.LoginID = ?LoginID
                    ) temp;
                   
                    ");
            if (!isDown)
            {
                sb.Append(" select distinct * from tempTblBranchID   order by BranchMark, BranchName desc; ");
            }
            else
            {
                sb.Append(@" 
                    
                   select distinct brr.ChildBranchID as BranchID,br.BranchMark,br.BranchName from tblpartyorgbranchrelation brr
                        left join tblpartyorgBranch br on(br.branchID=brr.ChildBranchID)
                        where brr.BranchID in (select BranchID from tempTblBranchID) ;
                ");
            }
            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters.ToArray());
        }

        /// <summary>
        /// 是否组织管理员
        /// </summary>
        /// <param name="loginId">用户ID</param>
        /// <param name="branchId">组织ID</param>
        /// <param name="isDown">是否包括下级组织</param>
        /// <returns></returns>
        public static bool IsBranchAdmin(string loginId, string branchId ,bool isDown = false)
        {
            var dt = GetBranchManageByLoginId(loginId, isDown);

            //return dt.Rows.Cast<DataRow>().Any(dr => Convert.ToString(dr["BranchID"]) == branchId);
            return dt.Rows.Count > 0;
        }
        /// <summary>
        /// 是否组织管理员
        /// </summary>
        /// <param name="loginId">用户ID</param>
        /// <param name="branchId">组织ID</param>
        /// <param name="isDown">是否包括下级组织</param>
        /// <returns></returns>
        public static bool IsBranchAdmin(string loginId, string branchId, out string outBranchId, bool isDown = false)
        {
            outBranchId = "";
            var dt = GetBranchManageByLoginId(loginId, isDown);
            if (dt.Rows.Count == 0)
            {
                return false;

            }
            if (dt.Rows.Cast<DataRow>().Any(dr => Convert.ToString(dr["BranchID"]) == branchId))
            {
                outBranchId = branchId;
                return true;

            }
            outBranchId = dt.Rows[0]["BranchID"].ToString();
            return true;
        }

        /// <summary>
        /// 初始化组织间的关系
        /// </summary>
        /// <returns></returns>
        public static void InitPartyOrgBranchRelation()
        {
            var sql = string.Format(@" call sp_InitBranchRelation(); ");
            MySqlHelper.ExecuteNonQuery(sql);
        }

        public static bool CheckLoginID(string loginID)
        {
            if (GetUserMessageByloginID(loginID).Rows.Count > 0)
                return false;
            else return true;
        }

        /// <summary>
        /// 根据用户ID获取用户信息
        /// </summary>
        /// <param name="loginID"></param>
        /// <returns></returns>
        public static DataTable GetUserMessageByloginID(string loginID)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "LoginID",
                    Value = loginID
                }
            };
            string sql = @"SELECT * FROM tblpartyorgmember WHERE LoginID =?LoginID";
            return MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());
        }

        public  static bool CheckIdentityCard(string identityCard)
        {
            if (GetUserMessageByIdentityCard(identityCard).Rows.Count > 0)
                return false;
            else return true;
        }
        /// <summary>
        /// 根据身份证号码获取用户信息
        /// </summary>
        /// <param name="identityCard"></param>
        /// <returns></returns>
        public  static DataTable GetUserMessageByIdentityCard(string identityCard)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>()
            {
                new MySqlParameter()
                {
                    DbType = DbType.String,
                    ParameterName = "IdentityCard",
                    Value = identityCard
                }
            };
            string sql = @"SELECT * FROM tblpartyorgmember WHERE IdentityCard =?IdentityCard";
            return MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());
        }

        

        /// <summary>
        /// 访问统计
        /// </summary>
        /// <returns></returns>
        public static void RPartyVisitStatistic()
        {
            var sql = string.Format(@" call sp_PartyVisitStatistic(); ");
            MySqlHelper.ExecuteNonQuery(sql);
        }

    }
}
